package cn.gson.oasys.model.dao.attendcedao;

import cn.gson.oasys.model.entity.attendce.Census;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;
@Repository
public interface CensusDao extends JpaRepository<Census,Long> {
    @Query(nativeQuery = true,value = "select num as num, deptId as deptId ,deptName as deptName," +
            "userId as userId,realName as realName from " +
            "(select count(1) as num ,dept.dept_id as deptId ,dept.dept_name as deptName,usr.user_id as userId,usr.real_name as realName from (\n" +
            "select t1.attends_id,t1.attends_time start_time,t2.attends_time end_time,t1.attends_user_id from(\n" +
            "(select attends_id,attends_time,attends_user_id from aoa_attends_list where type_id = 8) t1\n" +
            "left join \n" +
            "(select attends_id,attends_time,attends_user_id from aoa_attends_list where type_id = 9) t2\n" +
            "on left(t1.attends_time,10) = left(t2.attends_time,10) and t1.attends_user_id = t2.attends_user_id\n" +
            ") \n" +
            ")att left join aoa_user usr on usr.user_id = att.attends_user_id\n" +
            "left join aoa_dept dept on dept.dept_id = usr.dept_id\n" +
            "where att.start_time >= ?1 and att.end_time <= ?2\n" +
            "group by dept.dept_id,dept.dept_name,usr.user_id,usr.real_name) a \n" +
            "where 3 > (select count(1) from (select count(1) as num ,dept.dept_id as dept_id ,dept.dept_name,usr.user_id,usr.real_name from (\n" +
            "select t1.attends_id,t1.attends_time start_time,t2.attends_time end_time,t1.attends_user_id from(\n" +
            "(select attends_id,attends_time,attends_user_id from aoa_attends_list where type_id = 8) t1\n" +
            "left join \n" +
            "(select attends_id,attends_time,attends_user_id from aoa_attends_list where type_id = 9) t2\n" +
            "on left(t1.attends_time,10) = left(t2.attends_time,10) and t1.attends_user_id = t2.attends_user_id\n" +
            ") \n" +
            ")att left join aoa_user usr on usr.user_id = att.attends_user_id\n" +
            "left join aoa_dept dept on dept.dept_id = usr.dept_id\n" +
            "where att.start_time >= ?1 and att.end_time <= ?2\n" +
            "group by dept.dept_id,dept.dept_name,usr.user_id,usr.real_name) b \n" +
            "where a.deptId = b.dept_id and a.num < b.num ) order by deptId,num desc;")
    List<Census> findAttendsCensusByDate(String start_time, String end_time);

}
